<?php

/**
 * sql 语句拼装
 */

namespace LiteView\DB;


class SQLSuid
{
    public static $db = 'mysql';

    public static function updateOrInsert($table, $index, $values = [])
    {
        $condition = '';
        foreach ($index as $f => $v) {
            $v = addslashes($v);
            $condition .= "`$f` = \"$v\" AND ";
        }
        $condition = substr($condition, 0, -5);
        $exists = SQLPdo::db(self::$db)->query("SELECT count(1) as cnt FROM $table WHERE $condition")->fetch()['cnt'];
        if (!$exists) {
            return self::insert($table, array_merge($index, $values), true);
        }
        if ($values) {
            return self::update($table, $values, $condition);
        }
        return 0;
    }

    public static function insert($table, $data, $ignore = false)
    {
        $data_name = '';
        $data_value = '';
        foreach ($data as $field => $value) {
            $data_name .= "`$field`,";
            if (is_null($value)) {
                $data_value .= 'NULL,';
            } else {
                $value = addslashes($value);
                $data_value .= "\"$value\",";
            }
        }
        $data_name = substr($data_name, 0, -1);
        $data_value = substr($data_value, 0, -1);
        $ignore = $ignore ? 'ignore' : '';
        $sql = "INSERT $ignore INTO $table ($data_name) VALUES($data_value)";
        return SQLPdo::db(self::$db)->exec($sql, true); //返回插入ID
    }

    public static function update($table, $data, $condition, $prep = [])
    {
        $set = '';
        foreach ($data as $field => $value) {
            if (is_null($value)) {
                $set .= "`$field` = NULL,";
            } else {
                $value = addslashes($value);
                $set .= "`$field` = \"$value\",";
            }
        }
        if ('' === $set) {
            return 0;
        }
        $set = substr($set, 0, -1);
        $sql = "UPDATE $table SET $set WHERE $condition";
        return SQLPdo::db(self::$db)->prepare($sql, $prep)->rowCount();
    }

    public static function delete($table, $condition, $prep = [])
    {
        $sql = "DELETE FROM $table WHERE $condition";
        if ($prep) {
            return SQLPdo::db(self::$db)->prepare($sql, $prep)->rowCount();
        }
        return SQLPdo::db(self::$db)->exec($sql);
    }

    public static function select($table, $condition, $field = '*', $joins = [])
    {
        $join_str = '';
        foreach ($joins as $item) {
            $join_str .= "LEFT JOIN {$item['table']} ON {$item['on']} ";
        }
        $sql = "SELECT $field FROM $table $join_str WHERE $condition";
        return new self($sql);
    }

    private $params;
    private $sentence;

    public function __construct($sentence)
    {
        $this->params = [];
        $this->sentence = $sentence;
    }

    public function group($group)
    {
        $this->params['group'] = $group;
        return $this;
    }

    public function having($having)
    {
        $this->params['having'] = $having;
        return $this;
    }

    public function order($order)
    {
        $this->params['order'] = $order;
        return $this;
    }

    public function prep($prep = null)
    {
        //书写顺序：SELECT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> UNION -> ORDER BY ->LIMIT
        //执行顺序：FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> UNION -> ORDER BY ->LIMIT
        $P = $this->params;
        if (!empty($P['group'])) {
            $this->sentence .= " GROUP BY {$P['group']}";
        }
        if (!empty($P['having'])) {
            $this->sentence .= " HAVING {$P['having']}";
        }
        if (!empty($P['order'])) {
            $this->sentence .= " ORDER BY {$P['order']}";
        }

        if (!is_array($prep)) {
            $prep = [$prep];
        }
        return new SQLFetch($this->sentence, $prep, SQLPdo::db(SQLSuid::$db));
    }
}
